package lig.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import lig.com.conn.JdbcUtil;
import lig.com.dto.PrmDTO;
import lig.com.dto.PrmDTOList;


/**
 * *******************************
 * lig.com.dao
 * PrmDAO.java
 * @Description : Run premium query by agent
 * *******************************
 * @author : Euijoo Lee
 * Feb 12, 2013 4:21:47 PM
 */
public class PrmDAO {

private static PrmDAO instance = new PrmDAO();
	
	/**
	 * ************************************
	 * 1. Summary : get instance 
	 * ************************************
	 * @Method Name : getInstnace
	 * @return
	 */
	public static PrmDAO getInstnace() {
		return instance;
	}	

	/**
	 * ************************************
	 * 1. Summary : run query with yyyy1, yyyy2, agent ID
	 * ************************************
	 * @Method Name : prmMonthYTD
	 * @param conn
	 * @param yyyy1
	 * @param yyyy2
	 * @param agentID
	 * @return
	 * @throws SQLException
	 */
	public PrmDTOList prmMonthYTD(Connection conn, int yyyy1, int yyyy2, int yyyy3, String agentID) throws SQLException {

		StringBuffer sb = new StringBuffer();
		
		PrmDTOList PrmDtoList = new PrmDTOList();
		PreparedStatement pstmt = null;
		
		ResultSet rs = null;
		
		try {		
			
			sb.append("SELECT AGENT, SUM(YTDWRT)WRT, CAST(SUM(YTDERN) AS DECIMAL(12,4))ERN, SUBSTRING(TABLE_GROUP,9,6)ACTDTE ");
			sb.append("FROM ODS_DWXP056_BK.DBO.DWXP056ALL ");
			sb.append("WHERE SUBSTRING(TABLE_GROUP,9,4) in ");			
			sb.append("("+yyyy1+", "+yyyy2+", "+yyyy3+") ");
			sb.append(" and AGENT = ");
			sb.append("'"+agentID+"' ");
			sb.append(" GROUP BY AGENT,TABLE_GROUP ORDER BY AGENT");
			
			pstmt = conn.prepareStatement(sb.toString());
			
			
			rs = pstmt.executeQuery();

			while(rs.next()){
				PrmDTO PrmDto = new PrmDTO();				
				PrmDto.setAgent(rs.getString("AGENT"));
				PrmDto.setWrt(rs.getBigDecimal("WRT"));
				PrmDto.setErn(rs.getBigDecimal("ERN"));
				PrmDto.setActDate(rs.getInt("ACTDTE"));	
				//System.out.println("PrmDto.getActDate : "+PrmDto.getActDate());
				PrmDtoList.getDtoList().add(PrmDto);
				
			}
			
		}	
		catch(Exception e){
			e.printStackTrace();
		} finally {
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
		}
		return PrmDtoList;
	
	}
}
